package dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import util.DBHelper;
import orm.Teacher;
import orm.Users;

public class UserDAO {
	Connection conn = DBHelper.getConnection();
	Statement stmt;
	ResultSet rs;

	public Users UserLogin(String UserName, String Pwd) throws SQLException {
		Users user = new Users();
		stmt = conn.createStatement();
		rs = stmt.executeQuery("select * from users where UserName = '" + UserName + "'" + "and Pwd='" + Pwd + "'");
		while (rs.next()) {
			int i = 1;
			user.setUserID(rs.getInt(i++));
			user.setRoleID(rs.getInt(i++));
			user.setUserName(rs.getString(i++));
			user.setPwd(rs.getString(i++));
			user.setRealName(rs.getString(i++));
		}
		
		
//		stmt.close();
//		conn.close();
		return user;
	}
	public int UserRole(String userName) throws SQLException {
		conn = DBHelper.getConnection();
		stmt = conn.createStatement();
		rs = stmt.executeQuery("select RoleID from users where UserName = '" + userName + "'");
		rs.next();
		int roleID = rs.getInt(1);
		
		rs.close();
		stmt.close();
		conn.close();
		
		return roleID;
	}
	public boolean doesExist(String userName,String pwd) throws SQLException {
		stmt = conn.createStatement();
		rs = stmt.executeQuery("select count(*) from users where UserName = '" + userName + "'" + " and Pwd = '" + pwd + "'");
		rs.next();
		return rs.getInt(1) == 0 ? false : true;
	}
	public Users getUserID(int userID) throws SQLException {
		Users user = new Users();
		stmt = conn.createStatement();
		rs = stmt.executeQuery("select * from users where UserID =" + userID + "");
		while (rs.next()) {
			int i = 1;
			user.setUserID(rs.getInt(i++));
			user.setRoleID(rs.getInt(i++));
			user.setUserName(rs.getString(i++));
			user.setPwd(rs.getString(i++));
			user.setRealName(rs.getString(i++));
			user.setPosition(rs.getString(i++));
			user.setAuthorizationDate(rs.getString(i++));
			
		}
		
		return user;
	}
	public void insertUser(Users user) throws SQLException {
		stmt = conn.createStatement();
		stmt.execute("insert into users values(" + user.getRoleID() + ", '" + user.getUserName() + "', '" + 
				user.getPwd() + "', '" + user.getRealName() + "','" + user.getPosition() + "', '" + 
				user.getAuthorizationDate() + "')");
		
		stmt.close();
		conn.close();
	}
	public void updateUser(Statement statement, Users user) throws SQLException {
		statement.execute("update users set Pwd ='" + user.getPwd() + 
				"', RealName = '" + user.getRealName() + "', Position = '" + 
				user.getPosition() + "', AuthorizationDate = '" + user.getAuthorizationDate() + 
				"' where UserID = " + user.getUserID() + "");
	}
	public void deleteUser(Statement statement, int userID) throws SQLException {
		statement.execute("delete from users where UserID = " + userID + "");
	}
	public void deleteUser(Statement statement, Teacher teacher, String teacherID) throws SQLException {
		statement.execute("delete from users where UserID = " + teacher.getUserID() + "");
	}
	public ArrayList<Users> getAllUsers() throws SQLException {
		ArrayList<Users> al = new ArrayList<Users>();
		conn = DBHelper.getConnection();
		stmt = conn.createStatement();

		rs = stmt.executeQuery("select UserID, RealName, Position, AuthorizationDate from users where RoleID = 2 ORDER BY UserID ASC");
		Users user = null;
		
		while (rs.next()) {
			user = new Users();
			user.setUserID(rs.getInt("UserID"));
			user.setRealName(rs.getString("RealName"));
			user.setPosition(rs.getString("Position"));
			user.setAuthorizationDate(rs.getString("AuthorizationDate"));
		
			al.add(user);
		}
		
		stmt.close();
		conn.close();
		
		return al;
	}
	public boolean doesUserExist(String userName) throws SQLException {
		stmt = conn.createStatement();
		rs = stmt.executeQuery("select count(*) from users where UserName='" + userName + "'");
		rs.next();
		return rs.getInt(1) == 0 ? false : true;
	}

}
